#!/bin/sh
###############################################################################
##  Author    : xuezhoyi
##  Name      : edw_mysql_executor.sh
##  Functions : mysql执行器
##  Purpose   : Construct the interface of mysql
##  Revisions or Comments
##  VER        DATE        AUTHOR           DESCRIPTION
##---------  ----------  ---------------  ------------------------------------ 
##  1.0      2017-05-22  xuezhouyi        1. CREATED THIS SHELL.
##  2.0      2018-11-11  薛周毅           1.mysql执行器
###############################################################################

. ${0%`basename ${0}`}edw_parm.sh
. ${0%`basename ${0}`}edw_func.sh

function USAGE(){
    echo "[ERROR]How to use this shell script!"
    echo "[ERROR]${V_SHELL_HOME}/edw_db_executor.sh -q \"select current_date\""
    echo "[ERROR]${V_SHELL_HOME}/edw_db_executor.sh -t mysql -n FSD -p FSD -s 192.168.161.24:1521/DCITS -q \"select SYSDATE from dual\""
    echo "[ERROR]${V_SHELL_HOME}/edw_db_executor.sh -f \"/home/hadoop/dev/a.sql\""
}

# define basic parm
V_DB_USER=$(base64 -di ${V_DB_USER})
V_DB_PSWD=$(base64 -di ${V_DB_PSWD})

# run sql statement
function RUNSQL(){
  if [ "${V_DB_TYPE}"x == "mysql"x ]; then
    mysql -s -h${V_DB_HOST} -P${V_DB_PORT} -u${V_DB_USER} -p${V_DB_PSWD} --local-infile <<EOF
${1}
EOF
    SQLCODE=$?
    if [[ ${SQLCODE} -ne 0 ]]; then
      LOGGER "ERROR" "SQL执行失败！"
      LOGGER "DEBUG" "SQL=${1}"
      exit ${SQLCODE}
    fi
  elif [ "${V_DB_TYPE}"x == "db2"x ]; then
    db2 connect to ${V_DB_HOST} user ${V_DB_USER} using ${V_DB_PSWD} >/dev/null
    SQLCODE=$?
    if [[ ${SQLCODE} -ne 0 ]]; then
      LOGGER "ERROR" "db2连接失败"
      LOGGER "DEBUG" "db2 connect to ${V_DB_HOST} user ${V_DB_USER} using ${V_DB_PSWD}"
      exit ${SQLCODE}
    fi
    RESULT=$(db2 -x "${1}") 
    SQLCODE=$?
    if [[ ${SQLCODE} -eq 1 || ${SQLCODE} -eq 2  ]]; then
      exit 0
    elif [[ ${SQLCODE} -eq 0 ]];then
      echo ${RESULT}
      exit ${SQLCODE}
    else
      LOGGER "ERROR" "SQL执行失败！"
      LOGGER "DEBUG" "SQL=${1}"
      exit ${SQLCODE}
    fi
  fi
}

# run a sql file
function RUNFILE(){
  if [ "${V_DB_TYPE}"x == "mysql"x ]; then
    mysql -h${V_DB_HOST} -P${V_DB_PORT} -u${V_DB_USER} -p${V_DB_PSWD} -vv < ${1}
    SQLCODE=$?
    if [[ ${SQLCODE} -ne 0 ]]; then
      LOGGER "ERROR" "SQL执行失败！"
      LOGGER "DEBUG" "SQL=${1}"
      exit ${SQLCODE}
    fi
  elif [ "${V_DB_TYPE}"x == "db2"x ]; then
    db2 connect to ${V_DB_HOST} user ${V_DB_USER} using ${V_DB_PSWD} >/dev/null
    SQLCODE=$?
    if [[ ${SQLCODE} -ne 0 ]]; then
      LOGGER "ERROR" "db2连接失败"
      LOGGER "DEBUG" "db2 connect to ${V_DB_HOST} user ${V_DB_USER} using ${V_DB_PSWD}"
      exit ${SQLCODE}
    fi
    db2 -xstf ${1}
    SQLCODE=$?
    if [[ ${SQLCODE} -ne 0 ]]; then
      LOGGER "ERROR" "SQL执行失败！"
      LOGGER "DEBUG" "SQL=${1}"
      exit ${SQLCODE}
    fi
  fi
}

# export data
function EXPDATA(){
  if [ "${V_DB_TYPE}"x == "mysql"x ]; then
    mysql -s -h${V_DB_HOST} -P${V_DB_PORT} -u${V_DB_USER} -p${V_DB_PSWD} -e "${V_EXP_DATA}" 
    SQLCODE=$?
    if [[ ${SQLCODE} -ne 0 ]]; then
      LOGGER "ERROR" "SQL执行失败！"
      LOGGER "DEBUG" "SQL=${1}"
      exit ${SQLCODE}
    fi
  elif [ "${V_DB_TYPE}"x == "db2"x ]; then
    db2 connect to ${V_DB_HOST} user ${V_DB_USER} using ${V_DB_PSWD} >/dev/null
    SQLCODE=$?
    if [[ ${SQLCODE} -ne 0 ]]; then
      LOGGER "ERROR" "db2连接失败"
      LOGGER "DEBUG" "db2 connect to ${V_DB_HOST} user ${V_DB_USER} using ${V_DB_PSWD}"
      exit ${SQLCODE}
    fi
    RESULT=$(db2 -x "${V_EXP_DATA}")
    SQLCODE=$?
    if [[ ${SQLCODE} -eq 1 || ${SQLCODE} -eq 2 ]]; then
      exit 0
    elif [[ ${SQLCODE} -eq 0 ]];then
      echo ${RESULT}
      exit ${SQLCODE}
    else
      LOGGER "ERROR" "SQL执行失败！"
      LOGGER "DEBUG" "SQL=${V_EXP_DATA}"
      exit ${SQLCODE}
    fi
  fi
}

# get the parameter
while getopts :n:p:h:P:q:x:f: args; do
    case ${args} in
        n)
            V_DB_USER=${OPTARG}
        ;;
        p)
            V_DB_PSWD=${OPTARG}
        ;;
        h)
            V_DB_HOST=${OPTARG}
        ;;
        P)
            V_DB_PORT=${OPTARG}
        ;;
        q)
            V_SQL_QUERY=${OPTARG}
        ;;
        x)
            V_EXP_DATA=${OPTARG}
        ;;
        f)
            V_SQL_FILE=${OPTARG}
        ;;
        t)
            V_DB_TYPE=${OPTAGE}
        ;;
        ?)
            USAGE
        ;;
    esac
done

# ensure the basic variavle of connection is not null
if [[ -z ${V_DB_USER} || -z ${V_DB_PSWD} || -z ${V_DB_HOST} || -z ${V_DB_PORT} || -z ${V_DB_TYPE} ]];then
    USAGE
    exit 3
fi


# switch case
if [[ -f ${V_SQL_FILE} ]];then
    RUNFILE ${V_SQL_FILE}
elif [[ -n ${V_SQL_QUERY} ]];then
    RUNSQL "${V_SQL_QUERY}"
elif [[ -n ${V_EXP_DATA} ]];then
    EXPDATA
else
    USAGE
fi
